import copy
import json
import uuid
import random
import xlsxwriter
from io import BytesIO
from apis import api_routes
from flask import g, request, jsonify, abort, Response
from security.decoration import certification
from public.local import local_time
from datetime import datetime, timedelta
from sqlalchemy import and_
from typing import List
from typing import Union, Dict, Any
from sqlalchemy import text
from urllib.parse import unquote, quote
from models.order.tasks import Tasks
from models.product.routes import Routes
from models.course.detailed import Detailed
from models.facility.facility import Facility
from public.statistics import calculate_cpk
from models.product.aircraft import Aircraft


@api_routes.route("/query/facility", methods=["GET"])
@certification(auth=True, role=None)
def func_query_facility_get_api():
    """
    查询全部支持PLC设备, 有做分页，完成API开发
    http://127.0.0.1:8080/api/query/facility
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "index"         int      ****  第几页，默认为1
    "size"          int      ****  每页数据多少, 默认每页10条数据
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 数据报表
    """
    size = request.args.get("size", default=10, type=int)
    index = request.args.get("index", default=1, type=int)

    parm: Union[str, None] = None
    count = g.session.query(Facility.id).filter(Facility.uuid4 != parm).count()
    page: int = int((count / size) + 1) if (count / size) > int(count / size) else int(count / size)

    rep: list = []
    fts: List[Facility] = g.session.query(Facility).filter(
                                Facility.uuid4 != parm
                            ).order_by(Facility.id).offset((index - 1) * size).limit(size).all()
    fist_seq: bool = True
    for index, itr in enumerate(fts):
        if itr.uuid4 is not None:
            rep.append({
                "facility_id": itr.id,
                "serials": itr.serials,
                "name": itr.name,
                "default": fist_seq
            })
            fist_seq = False
    doc: dict = {
        "facility_id": "对应数据库ID",
        "serials": "设备编号NO",
        "name": "设备名字"
    }
    return jsonify({'status': 200, 'data': rep, "index": index, "size": size, "total": count, "page": page, "doc": doc})


@api_routes.route("/query/task/kind", methods=["GET"])
@certification(auth=True, role=None)
def func_query_task_kind_get_api():
    """
    支持模糊查询工单名称,(前端最好做响应式，输入一个子，调用我接口), 返回数组长度最大256，完成API开发
    http://127.0.0.1:8080/api/query/task/kind
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "name"       string     ****   工单名称
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 数据报表
    """
    name: Union[str, None] = request.args.get(key="name", default=None, type=str)
    if name is None or len(name) == 0:
        abort(400)

    resp: list = []
    sql_order: str = """
        SELECT 
            id, name 
        FROM tasks WHERE name LIKE '%{}%' LIMIT 256 OFFSET 0
    """
    sql_order = sql_order.format(name)
    data: list[tuple] = g.session.execute(text(sql_order)).fetchall()
    for task_id, itr in data:
        resp.append({
            "task_id": task_id,
            "name": itr
        })
    docs: dict = {
        "task_id": "对应数据库ID",
        "name": "工单名称",
    }
    return jsonify({'status': 200, 'data': resp, "docs": docs})


@api_routes.route("/query/aircraft/kind", methods=["GET"])
@certification(auth=True, role=None)
def func_query_aircraft_kind_get_api():
    """
    查询全部机种名称, 没有做分页，完成API开发
    http://127.0.0.1:8080/api/query/aircraft/kind
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 数据报表
    """
    resp: list = []
    db_data: List[Aircraft] = g.session.query(Aircraft).order_by(Aircraft.id).all()
    for data in db_data:
        resp.append({
            "aircraft_id": data.id,
            "aircraft": data.aircraft
        })

    docs: dict = {
        "aircraft_id": "对应数据库ID",
        "aircraft": "机种名称"
    }
    return jsonify({'status': 200, 'data': resp, "docs": docs})


@api_routes.route("/query/station", methods=["GET"])
# @certification(auth=True, role=None)
def func_query_station_get_api():
    """
    按照设备工站查询产量数据, (工单、机种、产品SN、日期可以自由组合查询) 完成API开发
    这个表数据量最多，可能上亿数据， 如果不指定日期，查询相对比较慢，前端最好做个默认日期（前面60天到今天）
    http://127.0.0.1:8080/api/query/station
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "facility_id"   int      ****   设备ID，没有此参数则是全部查询      /api/query/facility  GET
    "task_id"       int             工单名称ID，没有此参数则是全部查询   /api/query/task/kind GET
    "aircraft_id"   int             机种名称ID，没有此参数则是全部查询   /api/query/aircraft/kind GET
    "sequence"      string          产品SN，不支持模糊查询
    "start"         string          开始时间, 默认前面30天  格式 %Y-%m-%d
    "finish"        string          结束时间，默认今天时间  格式 %Y-%m-%d
    "index"         int      ****   第几页，默认为1
    "size"          int      ****   每页数据多少, 默认每页10条数据
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 数据报表
    """
    start: Union[str, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, None] = request.args.get("finish", default=None, type=str)
    size: int = request.args.get("size", default=10, type=int)
    index: int = request.args.get("index", default=1, type=int)

    facility_id: int = request.args.get("facility_id", default=0, type=int)
    task_id: Union[int, None] = request.args.get("task_id", default=0, type=int)
    arg_aircraft_id: Union[int, None] = request.args.get("aircraft_id", default=0, type=int)
    sequence: Union[str, None] = request.args.get("sequence", default=None, type=str)

    if sequence is not None and len(sequence) == 0:
        sequence = None

    if start is not None and len(start) != 0 and finish is not None and len(finish) != 0:
        arg0 = datetime.strptime(start, "%Y-%m-%d")
        arg1 = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)
        start: str = arg0.strftime("%Y-%m-%d %H:%M:%S")
        finish: str = arg1.strftime("%Y-%m-%d %H:%M:%S")
    else:
        start = None
        finish = None

    # sql_count: Union[str, None] = """
    #     SELECT
    #         COUNT(detailed.id)
    #     FROM detailed INNER JOIN tasks ON detailed.tasks_id = tasks.id
    #     INNER JOIN aircraft ON detailed.aircraft_id = aircraft.id
    #     WHERE detailed.id > 0 {} {} {} {} {}
    # """
    sql_count: Union[str, None] = """
        SELECT
            COUNT(detailed.id)
        FROM detailed WHERE detailed.id > 0 {} {} {} {} {}
    """
    # sql_context: Union[str, None] = """
    #     SELECT
    #         detailed.id, tasks.orders, tasks.name, aircraft.aircraft, detailed.sequence,
    #         coalesce(to_char(detailed.time,'yyyy-MM-dd hh24:MI:ss'), null) AS start,
    #         coalesce(to_char(detailed.finish,'yyyy-MM-dd hh24:MI:ss'), null) AS finish,
    #         detailed.message, detailed.status, detailed.numbers, detailed.error,
    #         detailed.option, facility.name, facility.id, detailed.mark
    #     FROM detailed INNER JOIN tasks ON detailed.tasks_id = tasks.id
    #     INNER JOIN aircraft ON detailed.aircraft_id = aircraft.id
    #     INNER JOIN facility ON detailed.facility_id = facility.id
    #     WHERE detailed.id > 0 {} {} {} {} {} ORDER BY detailed.id LIMIT :arg2 OFFSET :arg3
    # """

    sql_context: Union[str, None] = """
        SELECT
            detailed_fst.id, tasks.orders, tasks.name, aircraft.aircraft, detailed_fst.sequence,
            coalesce(to_char(detailed_fst.time,'yyyy-MM-dd hh24:MI:ss'), null) AS start,
            coalesce(to_char(detailed_fst.finish,'yyyy-MM-dd hh24:MI:ss'), null) AS finish,
            detailed_fst.message, detailed_fst.status, detailed_fst.numbers, detailed_fst.error,
            detailed_fst.option, facility.name, facility.id, detailed_fst.mark, detailed_fst.grade
        FROM
        (
            SELECT
                detailed.*
            FROM detailed WHERE detailed.id > 0 {} {} {} {} {} ORDER BY detailed.id LIMIT :arg2 OFFSET :arg3
        ) AS detailed_fst
        INNER JOIN tasks ON detailed_fst.tasks_id = tasks.id
        INNER JOIN aircraft ON detailed_fst.aircraft_id = aircraft.id
        INNER JOIN facility ON detailed_fst.facility_id = facility.id
        ORDER BY detailed_fst.id
    """

    parm1, parm2, parm3, parm4, parm5 = "", "", "", "", ""
    if facility_id != 0:
        parm1 = "AND detailed.facility_id = :ft_id"
    if task_id != 0:
        parm2 = "AND detailed.tasks_id = :tk_id"
    if arg_aircraft_id != 0:
        parm3 = "AND detailed.aircraft_id = :at_id"
    if sequence is not None:
        parm4 = "AND detailed.sequence = :seq"
    if start is not None and finish is not None:
        parm5 = "AND detailed.time >= :arg0 AND detailed.time <= :arg1"

    sql_count = sql_count.format(parm1, parm2, parm3, parm4, parm5)
    sql_context = sql_context.format(parm1, parm2, parm3, parm4, parm5)

    parm: dict = {
        "ft_id": facility_id, "at_id": arg_aircraft_id,
        "tk_id": task_id, "arg0": start, "arg1": finish,
        "seq": sequence, "arg2": size, "arg3": ((index - 1) * size)
    }

    rsp: list = []
    count: int = g.session.execute(text(sql_count), parm).fetchone()[0]
    page: int = int((count / size) + 1) if (count / size) > int(count / size) else int(count / size)
    data = g.session.execute(text(sql_context), parm).fetchall()

    for db_id, orders, name, aircraft, seq, start, finish, message, status, nbs, error, option, ft_n, ids, mark, grade2 in data:
        db_message_resp: list = []

        if message is not None:
            db_message: dict = json.loads(message)
            db_message: list = db_message.get("data")
            for itr in db_message:
                db_message_resp.append({
                    "key": itr.get("key"),
                    # "key_floor": "{}下限".format(itr.get("key")),
                    "value": itr.get("value"),
                    "floor": itr.get("floor"),
                    "upper": itr.get("upper"),
                    "uuid4": uuid.uuid4().hex
                })
        probably: dict = {
            1: "合格",
            2: "不合格",
            3: "报废"
        }
        rsp.append({
            "facility_id": ids,
            "facility_name": ft_n,
            "sequence": seq,
            "aircraft": aircraft,
            "task_name": name,
            "start": start,
            "finish": finish,
            "mark": probably.get(mark),
            "option": option,
            "db_message_resp": db_message_resp,
            "numbers": nbs,
            "grade": grade2,
            "error": error
        })

    doc: dict = {
        "facility_id": "设备ID",
        "sequence": "产品编码",
        "aircraft": "产品名称",
        "task_name": "工单任务名称",
        "start": "加工时间",
        "finish": "完成时间",
        "mark": "是否合格",
        "option": "操作员工",
        "grade": "打码等级",
        "error": "原因"
    }
    return jsonify({'status': 200, 'data': rsp, "index": index, "size": size, "total": count, "page": page, "doc": doc})


@api_routes.route("/query/station/output", methods=["GET"])
def func_query_station_output2_get_api():
    """
    按照设备工站条件查询导出EXCEL, (工单、机种、产品SN、日期可以自由组合查询) 完成API开发
    http://127.0.0.1:8080/api/query/station/output
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "file"         string           导出文件名
    "facility_id"   int             设备ID，没有此参数则是全部查询      /api/query/facility  GET
    "task_id"       int             工单名称ID，没有此参数则是全部查询   /api/query/task/kind GET
    "aircraft_id"   int             机种名称ID，没有此参数则是全部查询   /api/query/aircraft/kind GET
    "sequence"      string          产品SN，不支持模糊查询
    "start"         string          开始时间, 默认前面30天  格式 %Y-%m-%d
    "finish"        string          结束时间，默认今天时间  格式 %Y-%m-%d
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 数据报表
    """
    file: Union[str, None] = request.args.get("file", default=None, type=str)
    facility_id: int = request.args.get("facility_id", default=0, type=int)
    task_id: Union[int, None] = request.args.get("task_id", default=0, type=int)
    sequence: Union[str, None] = request.args.get("sequence", default=None, type=str)
    arg_aircraft_id: Union[int, None] = request.args.get("aircraft_id", default=0, type=int)
    start: Union[str, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, None] = request.args.get("finish", default=None, type=str)

    if start is not None and len(start) == 10 and finish is not None and len(finish) == 10:
        arg0 = datetime.strptime(start, "%Y-%m-%d")
        arg1 = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)
        start: str = arg0.strftime("%Y-%m-%d %H:%M:%S")
        finish: str = arg1.strftime("%Y-%m-%d %H:%M:%S")
    else:
        start, finish = None, None
    file_name = "条件查询产品SN报表{}.xlsx".format(local_time())
    if file is not None:
        file_name = "{}.xlsx".format(file)
    if sequence is not None and len(sequence) == 0:
        sequence = None

    sql_context: str = """
        SELECT
            tasks.orders, tasks.name, aircraft.aircraft, courses.sequence, 
        CASE
            WHEN courses.mark = 0 THEN '未加工完'
            WHEN courses.mark = 1 THEN '全部合格'
            WHEN courses.mark = 2 THEN '不合格'
            WHEN courses.mark = 3 THEN '报废产品'
            ELSE '未知测试结果'
        END AS sn_status,
            facility.id, facility.name,
            coalesce(to_char(detailed_fst.finish,'yyyy-MM-dd hh24:MI:ss'), null) AS finish,
        CASE
            WHEN detailed_fst.status = TRUE THEN '合格'
            WHEN detailed_fst.status = FALSE THEN '不合格'
            ELSE '未知结果'
        END AS ft_status,
            detailed_fst.numbers, detailed_fst.error, detailed_fst.message
        FROM
        (
            SELECT
                detailed.*
            FROM detailed WHERE detailed.id > 0 {} {} {} {} {} ORDER BY detailed.id LIMIT :arg2 OFFSET :arg3
        ) AS detailed_fst
        INNER JOIN tasks ON detailed_fst.tasks_id = tasks.id
        INNER JOIN aircraft ON detailed_fst.aircraft_id = aircraft.id
        INNER JOIN facility ON detailed_fst.facility_id = facility.id
        INNER JOIN courses ON detailed_fst.course_id = courses.id 
        ORDER BY detailed_fst.sequence, detailed_fst.id, detailed_fst.numbers
    """
    # parm6 = "detailed.sequence, detailed.id, detailed.numbers"
    parm1, parm2, parm3, parm4, parm5 = "", "", "", "", ""
    if facility_id != 0:
        parm1 = "AND detailed.facility_id = :ft_id"
    if task_id != 0:
        parm2 = "AND detailed.tasks_id = :tk_id"
    if arg_aircraft_id != 0:
        parm3 = "AND detailed.aircraft_id = :at_id"
    if sequence is not None:
        parm4 = "AND detailed.sequence = :seq"
    if start is not None and finish is not None:
        parm5 = "AND detailed.time >= :arg0 AND detailed.time <= :arg1"
    sql_context = sql_context.format(parm1, parm2, parm3, parm4, parm5)
    parm: dict = {
        "ft_id": facility_id, "at_id": arg_aircraft_id, "tk_id": task_id,
        "arg0": start, "arg1": finish, "seq": sequence, "arg2": 180000, "arg3": 0
    }
    db_data = g.session.execute(text(sql_context), parm).fetchall()
    output: BytesIO = BytesIO()
    workbook = xlsxwriter.Workbook(output, {'in_memory': True})
    worksheet_index = workbook.add_worksheet("工站数据导出信息表")

    worksheet_index.set_column("A:ZZ", width=22, cell_format=workbook.add_format({"align": "center", "valign": "vcenter"}))

    if facility_id != 0:
        message: list = [
            "工单编号", "任务名称", "机种类型", "产品序列号", "最终产品加工状态",
            "设备名称", "加工完成时间", "过站是否合格", "加工次数", "原因/备注信息"
        ]
        first_run: bool = True
        excel_index: int = 0
        hidden_array: List[int] = []
        for f_order, tk_name, f_aft, f_sn, f_status, ft_id, f_name, f_finish, f_qok, f_num, f_error, f_msg in db_data:
            if first_run is True:
                first_run = False
                if f_msg is not None:
                    fst_temp_index = 9
                    db_messages: dict = json.loads(f_msg)
                    db_messages: list = db_messages.get("data")
                    for itr in db_messages:
                        fst_temp_index = fst_temp_index + 1
                        hidden_array.append(fst_temp_index)
                        message.append("{}-下限".format(itr.get("key")))
                        fst_temp_index = fst_temp_index + 1
                        message.append("{}-实值".format(itr.get("key")))
                        fst_temp_index = fst_temp_index + 1
                        hidden_array.append(fst_temp_index)
                        message.append("{}-上限".format(itr.get("key")))
                worksheet_index.write_row(row=0, col=0, data=message)

            array: list = [f_order, tk_name, f_aft, f_sn, f_status, f_name, f_finish, f_qok, f_num, f_error]
            if f_msg is not None:
                db_messages: dict = json.loads(f_msg)
                db_messages: list = db_messages.get("data")
                for itr in db_messages:
                    array.append(itr.get("floor"))
                    array.append(itr.get("value"))
                    array.append(itr.get("upper"))
            excel_index = excel_index + 1
            worksheet_index.write_row(row=excel_index, col=0, data=array)
        for f_hidden in hidden_array:
            worksheet_index.set_column(first_col=f_hidden, last_col=f_hidden, options={'hidden': True})

        workbook.close()
        output.seek(0)
        resp = Response(output, content_type='application/octet-stream')
        resp.headers["Content-Disposition"] = "attachment; filename={0}; filename*=utf-8''{0}".format(quote(file_name))
        resp.headers.set("Access-Control-Expose-Headers", "*")
        return resp, 200

    global_index: int = 0
    message: list = [
        "工单编号", "任务名称", "机种类型", "产品序列号", "最终产品加工状态"
    ]
    ft_data: dict = {}
    ft_struct: dict = {}
    global_index = global_index + 4

    for ff_index, db_itr in enumerate(db_data):
        ft_data[db_itr[5]] = db_itr[11]
    sorted_dict = {k: ft_data[k] for k in sorted(ft_data)}
    for f_sorted_key, f_sorted_value in sorted_dict.items():

        global_index = global_index + 1
        ft_struct[f_sorted_key] = global_index

        message.append("加工设备名称")
        message.append("加工完成时间")
        message.append("过站是否合格")
        message.append("加工次数")
        message.append("原因/备注信息")

        global_index = global_index + 4

        if f_sorted_value is not None:
            lod_json = json.loads(f_sorted_value)
            temp_db_data = lod_json.get("data")
            for itr in temp_db_data:
                message.append("{}-下限".format(itr.get("key")))
                global_index = global_index + 1
                message.append("{}-实值".format(itr.get("key")))
                global_index = global_index + 1
                message.append("{}-上限".format(itr.get("key")))
                global_index = global_index + 1
    worksheet_index.write_row(row=0, col=0, data=message)

    is_first = True
    excel_line_index: int = 1
    sn_code, sn_number = "", 0
    for f_order, tk_name, f_aft, f_sn, f_status, ft_id, f_name, f_finish, f_qok, f_num, f_error, f_msg in db_data:
        if is_first is True:
            is_first = False
            sn_code, sn_number = f_sn, f_num
        if sn_code == f_sn and sn_number == f_num:
            worksheet_index.write_row(row=excel_line_index, col=0, data=[f_order, tk_name, f_aft, f_sn, f_status])
            ft_start = ft_struct[ft_id]
            excel_data = [f_name, f_finish, f_qok, f_num, f_error]
            if f_msg is not None:
                db_messages: dict = json.loads(f_msg)
                db_messages: list = db_messages.get("data")
                for itr in db_messages:
                    excel_data.append(itr.get("floor"))
                    excel_data.append(itr.get("value"))
                    excel_data.append(itr.get("upper"))
            worksheet_index.write_row(row=excel_line_index, col=ft_start, data=excel_data)
        else:
            sn_code, sn_number = f_sn, f_num
            worksheet_index.write_row(row=excel_line_index, col=0, data=[f_order, tk_name, f_aft, f_sn, f_status])
            ft_start = ft_struct[ft_id]
            excel_data = [f_name, f_finish, f_qok, f_num, f_error]
            if f_msg is not None:
                db_messages: dict = json.loads(f_msg)
                db_messages: list = db_messages.get("data")
                for itr in db_messages:
                    excel_data.append(itr.get("floor"))
                    excel_data.append(itr.get("value"))
                    excel_data.append(itr.get("upper"))
            worksheet_index.write_row(row=excel_line_index, col=ft_start, data=excel_data)

            excel_line_index = excel_line_index + 1


    for f_xx_index, f_xx_value in enumerate(message):
        if "-下限" in f_xx_value:
            worksheet_index.set_column(first_col=f_xx_index, last_col=f_xx_index, options={'hidden': True})
        if "-上限" in f_xx_value:
            worksheet_index.set_column(first_col=f_xx_index, last_col=f_xx_index, options={'hidden': True})

    workbook.close()
    output.seek(0)
    resp = Response(output, content_type='application/octet-stream')
    resp.headers["Content-Disposition"] = "attachment; filename={0}; filename*=utf-8''{0}".format(quote(file_name))
    resp.headers.set("Access-Control-Expose-Headers", "*")
    return resp, 200

